package model;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import java.util.logging.Level;
import java.util.logging.Logger;

import com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException;

/**
 * The DAO for accessing the database
 * @author Lawrence Grass, Kevin Hedglin
 */
public class GroceryListDao{
	
	private Connection connect = null;
	
	private PreparedStatement preparedStatement = null;
	
	private ResultSet rs = null;
	
	//change to whatever the name of your server
	private String url = "jdbc:mysql://localhost/shoppinglist";
	
	private String user = "user";
	
	private String pass = "pass";

	public GroceryListDao(){
		
	}
	
	public GroceryListDao(String user, String pass){
		this.user = user;
		this.pass = pass;
		
		
		url = "jdbc:mysql://localhost/shoppinglist";
		
	}
	
	public boolean checkConnection(String user, String pass){
		try{
			connect = DriverManager.getConnection(url, user, pass);
		} catch (SQLException e) {
			return false;
		}
		return true;
	}
	
	public void clearGroceryList() throws Exception{
		try
		{
			connect = DriverManager.getConnection(url, user, pass);
			preparedStatement = connect.prepareStatement("TRUNCATE TABLE shoppinglist");
			preparedStatement.executeUpdate();
			preparedStatement = connect.prepareStatement("TRUNCATE TABLE item");
			preparedStatement.executeUpdate();
		}
		catch (SQLException ex) 
		{
            Logger lgr = Logger.getLogger(GroceryListDao.class.getName());
            lgr.log(Level.SEVERE, ex.getMessage(), ex);
            
        } 
		finally 
		{
			try 
			{
                if (preparedStatement != null) 
                {
                    preparedStatement.close();
                }
                if (connect != null) 
                {
                    connect.close();
                }
            } 
			catch (SQLException ex) 
			{
                Logger lgr = Logger.getLogger(GroceryListDao.class.getName());
                lgr.log(Level.SEVERE, ex.getMessage(), ex);
            }
		}
		connect.close();
	}
	
	/**
	 * Gets the basic list of groceries for our shopping list
	 * @return the grocery list
	 * @throws Exception
	 */
	public List<GroceryItem> getGroceryList() throws Exception{
		List<GroceryItem> groceryitems = new ArrayList<GroceryItem>();
		try
		{
			connect = DriverManager.getConnection(url, user, pass);
			preparedStatement = connect.prepareStatement("SELECT * FROM item");
			rs = preparedStatement.executeQuery();
			while(rs.next()){
				GroceryItem item = new GroceryItem(rs.getString("item_name"), rs.getDouble("price"), rs.getBoolean("sale"));
				groceryitems.add(item);
			}
		}
		catch (SQLException ex) 
		{
            Logger lgr = Logger.getLogger(GroceryListDao.class.getName());
            lgr.log(Level.SEVERE, ex.getMessage(), ex);
            
        } 
		finally 
		{
			try 
			{
                if (preparedStatement != null) 
                {
                    preparedStatement.close();
                }
                if (connect != null) 
                {
                    connect.close();
                }
            } 
			catch (SQLException ex) 
			{
                Logger lgr = Logger.getLogger(GroceryListDao.class.getName());
                lgr.log(Level.SEVERE, ex.getMessage(), ex);
            }
		}
		connect.close();
		return groceryitems;
	}
	
	public List<HistoryItem> getHistory() throws Exception{
		List<HistoryItem> historyItems = new ArrayList<HistoryItem>();
		try
		{
			connect = DriverManager.getConnection(url, user, pass);
			preparedStatement = connect.prepareStatement("SELECT * FROM history");
			rs = preparedStatement.executeQuery();
			while(rs.next()){
				HistoryItem item = new HistoryItem(rs.getString("item_name"), rs.getInt("quantity"), rs.getDate("last_purchased"));
				item.toString();
				historyItems.add(item);
			}
		}
		catch (SQLException ex) 
		{
            Logger lgr = Logger.getLogger(GroceryListDao.class.getName());
            lgr.log(Level.SEVERE, ex.getMessage(), ex);
        } 
		finally 
		{
			try 
			{
                if (preparedStatement != null) 
                {
                    preparedStatement.close();
                }
                if (connect != null) 
                {
                    connect.close();
                }
            } 
			catch (SQLException ex) 
			{
                Logger lgr = Logger.getLogger(GroceryListDao.class.getName());
                lgr.log(Level.SEVERE, ex.getMessage(), ex);
            }
		}
		connect.close();
		return historyItems;
	}
	
	public List<NutritionItem> getNutrition() throws Exception{
		List<NutritionItem> nutritionItems = new ArrayList<NutritionItem>();
		try
		{
			connect = DriverManager.getConnection(url, user, pass);
			preparedStatement = connect.prepareStatement("SELECT * FROM nutrition");
			rs = preparedStatement.executeQuery();
			while(rs.next())
			{
				NutritionItem item = new NutritionItem(rs.getString("item_name"), rs.getInt("calories"),
						rs.getInt("total_fat"), rs.getInt("cholesterol"), rs.getInt("sodium"),
						rs.getInt("carbohydrates"), rs.getInt("protein"));
				item.toString();
				nutritionItems.add(item);
			}
		}
		catch (SQLException ex) 
		{
            Logger lgr = Logger.getLogger(GroceryListDao.class.getName());
            lgr.log(Level.SEVERE, ex.getMessage(), ex);
        } 
		finally 
		{
			try 
			{
                if (preparedStatement != null) 
                {
                    preparedStatement.close();
                }
                if (connect != null) 
                {
                    connect.close();
                }
            } 
			catch (SQLException ex) 
			{
                Logger lgr = Logger.getLogger(GroceryListDao.class.getName());
                lgr.log(Level.SEVERE, ex.getMessage(), ex);
            }
		}
		connect.close();
		return nutritionItems;
	}
	
	/**
	 * This method adds values to the item table of groceryapp
	 * @param name
	 * @param price
	 * @param sale
	 * @throws Exception
	 */
	public void addToItemTable(String name, double price, boolean sale) throws Exception 
	{
		try
		{
			connect = DriverManager.getConnection(url, user, pass);
			preparedStatement = connect.prepareStatement("INSERT INTO item VALUES(?, ?, ?)");
			preparedStatement.setString(1, name.trim());
			preparedStatement.setDouble(2, price);
			preparedStatement.setBoolean(3, sale);
			preparedStatement.executeUpdate();
		}
		catch (SQLException ex) 
		{
            Logger lgr = Logger.getLogger(GroceryListDao.class.getName());
            lgr.log(Level.SEVERE, ex.getMessage(), ex);
        } 
		finally 
		{
			try 
			{
                if (preparedStatement != null) 
                {
                    preparedStatement.close();
                }
                if (connect != null) 
                {
                    connect.close();
                }
            } 
			catch (SQLException ex) 
			{
                Logger lgr = Logger.getLogger(GroceryListDao.class.getName());
                lgr.log(Level.SEVERE, ex.getMessage(), ex);
            }
		}
		connect.close();
	}
	
	/**
	 * This method adds values to the itemLocation table
	 * @param item_name
	 * @param department
	 * @throws Exception
	 */
	public void addToItemLocationTable(String item_name, String department) throws Exception 
	{
		try
		{
			connect = DriverManager.getConnection(url, user, pass);
			preparedStatement = connect.prepareStatement("INSERT INTO itemlocation VALUES(?, ?)");
			preparedStatement.setString(1, item_name);
			preparedStatement.setString(2, department);
			preparedStatement.executeUpdate();
		}
		catch (SQLException ex) 
		{
            Logger lgr = Logger.getLogger(GroceryListDao.class.getName());
            lgr.log(Level.SEVERE, ex.getMessage(), ex);
        } 
		finally 
		{
			try 
			{
                if (preparedStatement != null) 
                {
                    preparedStatement.close();
                }
                if (connect != null) 
                {
                    connect.close();
                }
            } 
			catch (SQLException ex) 
			{
                Logger lgr = Logger.getLogger(GroceryListDao.class.getName());
                lgr.log(Level.SEVERE, ex.getMessage(), ex);
            }
		}
		connect.close();
	}
	
	/**
	 * This method adds values to the nutrition table
	 * @param item_name
	 * @param calories
	 * @param total_fat
	 * @param cholesterol
	 * @param sodium
	 * @param carbohydrates
	 * @param protein
	 * @throws Exception
	 */
	public void addToNutritionTable(String item_name, int calories, int total_fat, int cholesterol, int sodium,
									int carbohydrates, int protein) throws Exception 
	{
		try
		{
			connect = DriverManager.getConnection(url, user, pass);
			preparedStatement = connect.prepareStatement("INSERT INTO nutrition VALUES(?, ?, ?, ?, ?, ?, ?)");
			preparedStatement.setString(1, item_name);
			preparedStatement.setInt(2, calories);
			preparedStatement.setInt(3, total_fat);
			preparedStatement.setInt(4, cholesterol);
			preparedStatement.setInt(5, sodium);
			preparedStatement.setInt(6, carbohydrates);
			preparedStatement.setInt(7, protein);
			preparedStatement.executeUpdate();
		}
		catch (SQLException ex) 
		{
            Logger lgr = Logger.getLogger(GroceryListDao.class.getName());
            lgr.log(Level.SEVERE, ex.getMessage(), ex);
        } 
		finally 
		{
			try 
			{
                if (preparedStatement != null) 
                {
                    preparedStatement.close();
                }
                if (connect != null) 
                {
                    connect.close();
                }
            } 
			catch (SQLException ex) 
			{
                Logger lgr = Logger.getLogger(GroceryListDao.class.getName());
                lgr.log(Level.SEVERE, ex.getMessage(), ex);
            }
		}
		connect.close();
	}
	
	/**
	 * This method adds values to the shoppingList table
	 * @param item_name
	 * @param quantity
	 * @throws Exception
	 */
	public void addToShoppingListTable(String item_name, int quantity) throws Exception 
	{
		try
		{
			connect = DriverManager.getConnection(url, user, pass);
			preparedStatement = connect.prepareStatement("INSERT INTO shoppinglist VALUES(?, ?)");
			preparedStatement.setString(1, item_name);
			preparedStatement.setInt(2, quantity);
			preparedStatement.executeUpdate();
		}
		catch (SQLException ex) 
		{
            Logger lgr = Logger.getLogger(GroceryListDao.class.getName());
            lgr.log(Level.SEVERE, ex.getMessage(), ex);
        } 
		finally 
		{
			try 
			{
                if (preparedStatement != null) 
                {
                    preparedStatement.close();
                }
                if (connect != null) 
                {
                    connect.close();
                }
            } 
			catch (SQLException ex) 
			{
                Logger lgr = Logger.getLogger(GroceryListDao.class.getName());
                lgr.log(Level.SEVERE, ex.getMessage(), ex);
            }
		}
		connect.close();
	}
	
	/**
	 * This method adds values to the itemLocation table
	 * @param item_name
	 * @param department
	 * @throws Exception
	 */

	public void addToHistoryTable(String item_name, int quantity) throws Exception 
	{
		try
		{
			connect = DriverManager.getConnection(url, user, pass);
			preparedStatement = connect.prepareStatement("INSERT INTO history VALUES(?, ?, ?)");
			preparedStatement.setString(1, item_name);
			preparedStatement.setInt(2, quantity);
			preparedStatement.setDate(3, new java.sql.Date(System.currentTimeMillis()));
			preparedStatement.executeUpdate();
		
		} catch (MySQLIntegrityConstraintViolationException ex){
			System.out.println("already in the history");
		}
		catch (SQLException ex) 
		{
            Logger lgr = Logger.getLogger(GroceryListDao.class.getName());
            lgr.log(Level.SEVERE, ex.getMessage(), ex);
        } 
		finally 
		{
			try 
			{
                if (preparedStatement != null) 
                {
                    preparedStatement.close();
                }
                if (connect != null) 
                {
                    connect.close();
                }
            } 
			catch (SQLException ex) 
			{
                Logger lgr = Logger.getLogger(GroceryListDao.class.getName());
                lgr.log(Level.SEVERE, ex.getMessage(), ex);
            }
		}
		connect.close();
	}
	
	public boolean canAddItem(String item)
	{
		int found = 0;
		String item2 = item;
		if(item.contains("'")){
			item2 = item.replace("'", "\\'");
		}
		try 
		{
			connect = DriverManager.getConnection(url, user, pass);
			Statement stmnt = connect.createStatement();
			ResultSet rs = stmnt.executeQuery("SELECT COUNT(*) AS names FROM item WHERE item_name = " + "'" + item2 + "'");
			if(rs.next()){
			found = rs.getInt("names");
			}
			connect.close();
			if(found < 1)
			{
				return true;
			}
		}
		catch (SQLException ex) 
		{
            Logger lgr = Logger.getLogger(GroceryListDao.class.getName());
            lgr.log(Level.SEVERE, ex.getMessage(), ex);
        }
		return false;
	}
	
	public boolean canAddNutrition(String item)
	{
		int found = 0;
		String item2 = item;
		if(item.contains("'")){
			item2 = item.replace("'", "\\'");
		}
		try 
		{
			connect = DriverManager.getConnection(url, user, pass);
			Statement stmnt = connect.createStatement();
			ResultSet rs = stmnt.executeQuery("SELECT COUNT(*) AS names FROM nutrition WHERE item_name = " + "'" + item2 + "'");
			if(rs.next()){
			found = rs.getInt("names");
			}
			connect.close();
			if(found < 1)
			{
				return true;
			}
		}
		catch (SQLException ex) 
		{
            Logger lgr = Logger.getLogger(GroceryListDao.class.getName());
            lgr.log(Level.SEVERE, ex.getMessage(), ex);
        }
		return false;
	}
	
	public void deleteItem(String name)
	{
		if(name.contains("'")){
			name = name.replace("'", "\\'");
		}
		try 
		{
			
			connect = DriverManager.getConnection(url, user, pass);
			preparedStatement = connect.prepareStatement("DELETE FROM item WHERE item_name = " + "'" + name + "'");
			preparedStatement.executeUpdate();
			preparedStatement = connect.prepareStatement("DELETE FROM history WHERE item_name = " + "'" + name + "'");
			preparedStatement.executeUpdate();
			preparedStatement = connect.prepareStatement("DELETE FROM itemlocation WHERE item = " + "'" + name + "'");
			preparedStatement.executeUpdate();
			preparedStatement = connect.prepareStatement("DELETE FROM shoppinglist WHERE item = " + "'" + name + "'");
			preparedStatement.executeUpdate();
			connect.close();
		}
		catch (SQLException ex) 
		{
            Logger lgr = Logger.getLogger(GroceryListDao.class.getName());
            lgr.log(Level.SEVERE, ex.getMessage(), ex);
        } 
		
	}
	
	public void deleteNutrition(String name)
	{
		if(name.contains("'")){
			name = name.replace("'", "\\'");
		}
		try 
		{
			
			connect = DriverManager.getConnection(url, user, pass);
			preparedStatement = connect.prepareStatement("DELETE FROM nutrition WHERE item_name = " + "'" + name + "'");
			preparedStatement.executeUpdate();
			connect.close();
		}
		catch (SQLException ex) 
		{
            Logger lgr = Logger.getLogger(GroceryListDao.class.getName());
            lgr.log(Level.SEVERE, ex.getMessage(), ex);
        } 
		
	}
}